
var mysql  = require('mysql');

var redirect_formatTable = 
{
	"001":"&dv1={click_id}&nw_sub_aff={aff_pub}",// avaz
	"002":"/{click_id}&placement={aff_pub}", // gli
	"004":"&subid1={click_id}&placement={aff_pub}", // mundu
	"009":"&s1={click_id}&s2={aff_pub}", // leadhug
	"010":"&aff_sub={click_id}&aff_sub8={aff_pub}", // yeahmobi
	"014":"&aff_sub={click_id}&aff_sub2={aff_pub}", // appflood
	"017":"&aff_sub={click_id}&source={aff_pub}", // blackfox
	"021":"&aff_sub={click_id}&aff_sub2={aff_pub}", // zhihe
}

var postback_formatTable = 
{	
	"000":"add688.com",// 自己
	"003":"http://next.mobvista.com/install?mobvista_clickid={aff_sub}&mobvista_campuuid={aff_offer}&mb_ip={ip}",// mobvi
}

var camType1Table = 
{
	"Android":'GP-CPI',
	"IOS":'APP-STORE',
	"Android/IOS":'GP-CPI/APP-STORE',
}

var dataDefaultTable = 
{
	//lib_offer
	offer_name:'',   
	group_id:0, 
    advertiser_id:'001',
    adv_camp_id:0,       
    first_pay_in:0,
    pay_type:'cpi',   
    redirect_format:'',
    start_date:'2016-01-01 00:00',    
    end_date:'2017-01-01 00:00',   
    geo_country:'全球用global表示',
    geo_city:'global',          
    geo_country_except:'',
    geo_city_except:'', 
    cap_all_today:'open的话填1000000否则填真实值',
    cap_all:'',  //不填则默认和cap all today 一样  不要默认null因为是text的输入框 默认‘’
    ret_day_need:0,      
    ret_percent_need:0, 
    quality0:'',          
    quality1:'',          
    quality2:'',          
    quality3:'',          
    quality4:'',       
    quality5:'',
    traffic_type:'in-app',      
    traffic_reqirement:'',
    kpi:'',
    platform:'Android',          
    devices:'phones/tables', 
    osversion:'',  
    appid:'',
    operator:'gnar',
    payment_terms:30,
    preview_link:'',  
    pack_res_link:'',     
    slogan_description:'',
    remark0:'',
    remark1:'',

    //lib_campaign
	offer_id:'',
	aff_id:'003',
	payin:0, 
	payout:'', //不填则默认和payin 一样
 	state:1,
	//start_date:'2016-01-01 00:00',    
	//end_date:'2017-01-01 00:00',  
	//redirect_format:null,
	//geo_country:null,
	//geo_city:'global',
	//geo_country_except:'',
	//geo_city_except:'',
	//cap_all_today:'这里填真实值*100', 
	cap_exceed_count:0,  
	cap_exceed_percent:0,
	force_stop_level:0,  
	force_stop:0,      
	//remark0:null,           
	//remark1:null,

	//interface_info_v1
	pid:'003', 
	campid:'',
	//offer_name:null, 
	status:'running' ,     
	//state:1 ,
	tips:'',
	//platform:'Android',
	price_model:'cpi',	 
	price:0,       
	geo:'格式：国家A 国家B ... except 国家C 国家D(global表示全球)',
	tracking_link:'',
	//preview_link:null,
	//start_date:'2016-01-01 00:00',    
    //end_date:'2017-01-01 00:00',  
	performance_criteria:'',
	requirement_traffic:'',
	effective_date: '2016-01-01 00:00',        
	update_date: CurentTime() ,  
	app_category:'Tool',
	exclude_device:'',     
	exclude_site:0,  
	carriers:'',
	daily_cap:'open的话填1000000否则填真实值',
	creative_link:'',
	icon_link:'',           
	min_android_version:'', 
	max_android_version:'none', 
	app_name:'',            
	app_desc:'',            
	app_size:0,            
	app_rate:0, 

}


function getSqlConnection()
{
	var connection = mysql.createConnection({host: 'rdsea0aj9n7m3g48ta5u.mysql.rds.aliyuncs.com', database: 'ad688com', user:'ad688com',password : 'ad688com',port: '3306',dateStrings: true ,}); 
	//var connection = mysql.createConnection({host: 'game1715.com', database: 'test', user:'t',password : 's123',port: '3306',dateStrings: true ,}); 
	return connection
}

function CurentTime()
{ 
		var now   = new Date();
		var year  = now.getFullYear();       //年
		var month = now.getMonth() + 1;     //月
		var day   = now.getDate();            //日
		var hh    = now.getHours();            //时
		var mm    = now.getMinutes();          //分
        var clock = year + "-";
        if(month < 10)
            clock += "0";
        clock += month + "-";
        if(day < 10)
            clock += "0";
        clock += day + " ";
        if(hh < 10)
            clock += "0";
        clock += hh + ":";
        if (mm < 10) clock += '0'; 
        clock += mm; 
        return(clock); 
} 


//////////////lib_offer////////////////////////////

// console.log(module.exports === this);  
// console.log(module.exports === exports);  
// console.log(this === exports);  

this.getLibOfferDataDefault =  function getLibOfferDataDefault()
{
  var lib_offer={
	offer_name:dataDefaultTable.offer_name,   
	group_id:dataDefaultTable.group_id, 
    advertiser_id:dataDefaultTable.advertiser_id,
    adv_camp_id:dataDefaultTable.adv_camp_id,       
    first_pay_in:dataDefaultTable.first_pay_in,
    pay_type:dataDefaultTable.pay_type,   
    redirect_format:dataDefaultTable.redirect_format,
    start_date:dataDefaultTable.start_date,    
    end_date:dataDefaultTable.end_date,   
    geo_country:dataDefaultTable.geo_country,
    geo_city:dataDefaultTable.geo_city,          
    geo_country_except:dataDefaultTable.geo_country_except,
    geo_city_except:dataDefaultTable.geo_city_except, 
    cap_all_today:dataDefaultTable.cap_all_today,
    cap_all:dataDefaultTable.cap_all,  
    ret_day_need:dataDefaultTable.ret_day_need,      
    ret_percent_need:dataDefaultTable.ret_percent_need, 
    quality0:dataDefaultTable.quality0,          
    quality1:dataDefaultTable.quality1,          
    quality2:dataDefaultTable.quality2,          
    quality3:dataDefaultTable.quality3,          
    quality4:dataDefaultTable.quality4,          
    quality5:dataDefaultTable.quality5,
    traffic_type:dataDefaultTable.traffic_type,      
    traffic_reqirement:dataDefaultTable.traffic_reqirement,
    kpi:dataDefaultTable.kpi,
    platform:dataDefaultTable.platform,          
    devices:dataDefaultTable.devices, 
    osversion:dataDefaultTable.osversion,  
    appid:dataDefaultTable.appid,
    operator:dataDefaultTable.operator,
    payment_terms:dataDefaultTable.payment_terms,
    preview_link:dataDefaultTable.preview_link,  
    pack_res_link:dataDefaultTable.pack_res_link,     
    slogan_description:dataDefaultTable.slogan_description,
    remark0:dataDefaultTable.remark0,
    remark1:dataDefaultTable.remark1,
  }

 return lib_offer
}

//从sql中读取对应offer_id的libofferData
exports.getLibOfferDataOfSql = function getLibOfferDataOfSql(offer_id, onGet)
{
	var connection = getSqlConnection()
	var sql = 'SELECT * FROM lib_offer WHERE offer_id = ?';
	var params = [offer_id];

	connection.query(sql, params,function (err, rows) 
	{
        if(err)
        {
          	console.log('[SELECT ERROR] - ',err.message);
          	connection.end();
			var result = { selectSuccess: false}
		   	onGet(result)	
		   	return   
        }        
        console.log('[SELECT success] - ',rows);  

		connection.end();
		var result = { selectSuccess: true, body: rows[0]}
		onGet(result)	   
	});

}

//将表单的数据录入sql 并设置offer_id
exports.writeLibOfferToSql = function writeLibOfferToSql(data, onWrite)
{
	//过滤非录入数据 
	data.id = null //null自增
	data.offer_id = '' //录入完成后计算
	data.cap_run_today = 0 //运行时变量
	data.cap_run = 0 //运行时变量
	//填充data
	if(data.cap_all==='') 
		data.cap_all = data.cap_all_today

	var connection = getSqlConnection()
	connection.connect();  
	//执行SQL语句
	var element =['id', 'offer_id', 'offer_name', 'group_id', 'advertiser_id', 'adv_camp_id', 'first_pay_in', 'pay_type', 'redirect_format', 'start_date', 'end_date', 'geo_country', 'geo_city', 'geo_country_except', 'geo_city_except', 'cap_all_today', 'cap_run_today', 'cap_all', 'cap_run', 'ret_day_need', 'ret_percent_need', 'quality0', 'quality1', 'quality2', 'quality3', 'quality4', 'quality5', 'traffic_type', 'traffic_reqirement', 'kpi', 'platform', 'devices', 'osversion', 'appid', 'operator', 'payment_terms', 'preview_link', 'pack_res_link', 'slogan_description', 'remark0', 'remark1']
	var sql1 = '('
	for (var i=0;i<element.length;i++)
	{
		sql1 = sql1 + element[i] 
		if (i == element.length -1)
			sql1 = sql1 + ')'
		else
			sql1 = sql1 + ','
	}
	console.log('INSERT sql1:',sql1);     
	var  sql = 'INSERT INTO lib_offer' + sql1 + 'VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
	var  params = [];
	for (var i=0;i<element.length;i++)
	{
		if(element[i]== 'geo_country' && data[element[i]] =='global')
			params[i] = 'AO AF AL DZ AD AI AG AR AM AU AT AZ BS BH BD BB BY BE BZ BJ BO BW BR BN BG BF BM BT MM BI CM CA CF TD CL CN CO CG KH CR CU CY CZ CK DK DJ DO EC EG SV EE ET FJ FI FR GF GA GM GE DE GH GI GR GD GU GT GN GY HT HN HK HU IS IN ID IR IQ IE IL IT KT JM JP JO KZ KE KR KW KG KH LA LV LB LS LR LY LI LT LU MO MG MW MS MY MV ML MT MU MR MX MD MC MN MA MZ MM NA NZ NR NP NL NI NE NG KP NO OM PK PA PG PY PE PH PL PF PT PR QA RO RU LC VC SM ST SA SN SC SL SG SK SI SB SO ZA ES LK LC VC SD SR SZ SE CH SY TW TJ TZ TH TG TO TT TL TN TR TM UG UA AE GB US UY UZ TZ VE VN YE YU ZA ZW ZR ZM'
		else
			params[i] = data[element[i]]
		console.log('INSERT params:',element[i],params[i],data[element[i]]);     
	}

	connection.query(sql,params, 
		function(err, rows) 
		{ 
		    if (err) 
		    {
		        console.log('INSERT error:',err.message);
		        connection.end();
		      	var result =  {insertSuccess: false, message: 'insert lib_offer fail'}
		      	onWrite(result)
		      	return
		    }
		    console.log('INSERT success:',rows);    

		    var insertId = rows.insertId

			var sql = 'UPDATE lib_offer SET offer_id = ? WHERE id = ?';
			var params = [insertId + 100000, insertId];

			connection.query(sql,params,function (err, rows) 
			{
				if(err){
					console.log('UPDATE error:',err.message);
					connection.end();
					var result =  {insertSuccess: true, outData:{}, message: 'insert lib_offer success,update offer_id fail'}
		      		onWrite(result)	
		      		return
				}        
				console.log('UPDATE success',rows);

				connection.end();
				var offerId = insertId + 100000
				var out = {offer_id:offerId,}
				var result = { insertSuccess: true, outData:out, message: 'insert lib_offer success,offer_id:' + offerId}
		      	onWrite(result)	
			});
		 }
	); 

}

//////////////lib_campaign////////////////////////////
exports.getLibCampaignDefault =  function getLibCampaignDefault()
{
  var lib_campaign={
  	offer_id:dataDefaultTable.offer_id,
	aff_id:dataDefaultTable.aff_id,
	payin:dataDefaultTable.payin, 
	payout:dataDefaultTable.payout,
 	state:dataDefaultTable.state,
	start_date:dataDefaultTable.start_date,    
	end_date:dataDefaultTable.end_date,  
	redirect_format:dataDefaultTable.redirect_format,
	geo_country:dataDefaultTable.geo_country,
	geo_city:dataDefaultTable.geo_city,
	geo_country_except:dataDefaultTable.geo_country_except,
	geo_city_except:dataDefaultTable.geo_city_except,
	cap_all_today:dataDefaultTable.cap_all_today, 
	cap_exceed_count:dataDefaultTable.cap_exceed_count,  
	cap_exceed_percent:dataDefaultTable.cap_exceed_percent,
	force_stop_level:dataDefaultTable.force_stop_level,  
	force_stop:dataDefaultTable.force_stop,      
	remark0:dataDefaultTable.remark0,           
	remark1:dataDefaultTable.remark1,
  }

 return lib_campaign
}


exports.getLibCampaignDataOfSql = function getLibCampaignDataOfSql(campaign_id, onGet)
{
	var connection = getSqlConnection()
	var sql = 'SELECT * FROM lib_campaign WHERE campaign_id = ?';
	var params = [campaign_id];

	connection.query(sql, params,function (err, rows) 
	{
        if(err)
        {
          	console.log('[SELECT ERROR] - ',err.message);
          	connection.end();
			var result = { selectSuccess: false}
		   	onGet(result)	   
		   	return
        }        
        console.log('[SELECT success] - ',rows);  

		connection.end();
		var result = { selectSuccess: true, body: rows[0]}
		onGet(result)	   
	});

}

exports.getLibCampaignByLibOffer = function getLibCampaignByLibOffer(offer_id, onGet)  
{
	var env = this
	function onGetLibOfferDataOfSql(result)
	{
		if(result.selectSuccess && result.body) 
		{
			var lib_campaign                = env.getLibCampaignDefault()
			lib_campaign.offer_id           =result.body.offer_id          
			lib_campaign.payin              =result.body.first_pay_in            
			lib_campaign.payout             =result.body.first_pay_in           
			lib_campaign.start_date         =result.body.start_date         
			lib_campaign.end_date           =result.body.end_date           
			lib_campaign.redirect_format    =result.body.redirect_format + (redirect_formatTable[result.body.advertiser_id] || '&aff_sub={click_id}&aff_sub2={aff_pub}')   
			lib_campaign.geo_country        =result.body.geo_country        
			lib_campaign.geo_city           =result.body.geo_city          
			lib_campaign.geo_country_except =result.body.geo_country_except 
			lib_campaign.geo_city_except    =result.body.geo_city_except    
			lib_campaign.cap_all_today      =result.body.cap_all_today     
			var result_getLibCampaignByLibOffer = {selectSuccess: true, body: lib_campaign}
		   	onGet(result_getLibCampaignByLibOffer)	
		}
		else
		{
			var result_getLibCampaignByLibOffer = {selectSuccess: false}
		   	onGet(result_getLibCampaignByLibOffer)	
		}
	}

	this.getLibOfferDataOfSql(offer_id,onGetLibOfferDataOfSql) //export 的函数相当于对象的属性 必须要this.
}

exports.writeLibCampaignToSql = function writeLibCampaignToSql(data, onWrite)
{	

	//过滤非录入数据
	data.id = null
	data.campaign_id = '' //录入完成后计算
	data.cap_run_today = 0
	data.cap_run = 0
	data.tracking_link = '' //录入完成后计算
	data.postback_format = '' //录入完成后计算

	//填充
	if (data.payout==='')
		data.payout = data.payin

	var connection = getSqlConnection()
	connection.connect();  
	var element =[ 'id', 'campaign_id', 'offer_id', 'aff_id', 'payin', 'payout', 'state', 'start_date', 'end_date', 'redirect_format', 'postback_format', 'geo_country', 'geo_city', 'geo_country_except', 'geo_city_except', 'tracking_link', 'cap_all_today', 'cap_run_today', 'cap_run', 'cap_exceed_count', 'cap_exceed_percent', 'force_stop_level', 'force_stop', 'remark0', 'remark1']
	var sql1 = '('
	for (var i=0;i<element.length;i++)
	{
		sql1 = sql1 + element[i] 
		if (i == element.length -1)
			sql1 = sql1 + ')'
		else
			sql1 = sql1 + ','
	}
	console.log('INSERT sql1:',sql1);     
	var  sql = 'INSERT INTO lib_campaign' + sql1 + 'VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
	var  params = [];
	for (var i=0;i<element.length;i++)
	{	

		if(element[i]== 'geo_country' && data[element[i]] =='global')
			params[i] = 'AO AF AL DZ AD AI AG AR AM AU AT AZ BS BH BD BB BY BE BZ BJ BO BW BR BN BG BF BM BT MM BI CM CA CF TD CL CN CO CG KH CR CU CY CZ CK DK DJ DO EC EG SV EE ET FJ FI FR GF GA GM GE DE GH GI GR GD GU GT GN GY HT HN HK HU IS IN ID IR IQ IE IL IT KT JM JP JO KZ KE KR KW KG KH LA LV LB LS LR LY LI LT LU MO MG MW MS MY MV ML MT MU MR MX MD MC MN MA MZ MM NA NZ NR NP NL NI NE NG KP NO OM PK PA PG PY PE PH PL PF PT PR QA RO RU LC VC SM ST SA SN SC SL SG SK SI SB SO ZA ES LK LC VC SD SR SZ SE CH SY TW TJ TZ TH TG TO TT TL TN TR TM UG UA AE GB US UY UZ TZ VE VN YE YU ZA ZW ZR ZM'
		else
			params[i] = data[element[i]]

		console.log('INSERT params:',params[i]);     
	}

	connection.query(sql,params, 
		function(err, rows) 
		{ 
		    if (err) 
		    {
		        console.log('INSERT error:',err.message);
		        connection.end();
		      	var result =  {insertSuccess: false,message: 'insert lib_campaign fail'}
		      	onWrite(result)
		      	return
		    }
		    console.log('INSERT success:',rows);    

		    var insertId = rows.insertId

			var sql = 'UPDATE lib_campaign SET campaign_id = ?,tracking_link = ?,postback_format = ? WHERE id = ?';
			var campaign_id = insertId + 100000
			var tracking_link = 'http://ad688.com/offer/' + data.aff_id + '/' + campaign_id + '/?aff_offer={campaign}&aff_pub={publisher}&aff_sub={click_id}'

			var postback_format = postback_formatTable[data.aff_id] || postback_formatTable['000']
			var params = [campaign_id, tracking_link, postback_format, insertId];

			connection.query(sql,params,function (err, rows) 
			{
				if(err){
					console.log('UPDATE error:',err.message);
					connection.end();
					var result =  {insertSuccess: true, outData:{}, message: 'insert lib_campaign success,update campaign_id fail'}
		      		onWrite(result)	
		      		return
				}        
				console.log('UPDATE success',rows);

				connection.end();
				var campaign_id = insertId + 100000
				var out = {campaign_id:campaign_id, tracking_link:tracking_link,postback_format:postback_format, }
				var result = { insertSuccess: true, outData:out, message: 'insert lib_campaign success,campaign_id:' + campaign_id}
		      	onWrite(result)	
			});
		 }
	); 
}

//////////////interface_info_v1////////////////////////////

exports.getInterfaceInfoDefault =  function getInterfaceInfoDefault()
{
  var interface_info_v1={
	  	pid:dataDefaultTable.pid, 
		campid:dataDefaultTable.campid,
		offer_name:dataDefaultTable.offer_name, 
		status:dataDefaultTable.status ,     
		state:dataDefaultTable.state ,
		tips:dataDefaultTable.tips,
		platform:dataDefaultTable.platform,
		price_model:dataDefaultTable.price_model,	 
		price:dataDefaultTable.price,       
		geo:dataDefaultTable.geo,
		tracking_link:dataDefaultTable.tracking_link,
		preview_link:dataDefaultTable.preview_link,
		start_date:dataDefaultTable.start_date,    
	    end_date:dataDefaultTable.end_date,  
		performance_criteria:dataDefaultTable.performance_criteria,
		requirement_traffic:dataDefaultTable.requirement_traffic,
		effective_date: dataDefaultTable.effective_date,        
		update_date: dataDefaultTable.update_date ,  
		app_category:dataDefaultTable.app_category,
		exclude_device:dataDefaultTable.exclude_device,     
		exclude_site:dataDefaultTable.exclude_site,  
		carriers:dataDefaultTable.carriers,
		daily_cap:dataDefaultTable.daily_cap,
		creative_link:dataDefaultTable.creative_link,
		icon_link:dataDefaultTable.icon_link,           
		min_android_version:dataDefaultTable.min_android_version, 
		max_android_version:dataDefaultTable.max_android_version, 
		app_name:dataDefaultTable.app_name,            
		app_desc:dataDefaultTable.app_desc,            
		app_size:dataDefaultTable.app_size,            
		app_rate:dataDefaultTable.app_rate, 

  }

 return interface_info_v1
}

exports.getInterfaceInfoDataOfSql = function getLibCampaignDataOfSql(id, onGet)
{
	var connection = getSqlConnection()
	var sql = 'SELECT * FROM interface_info_v1 WHERE id = ?';
	var params = [id];

	connection.query(sql, params,function (err, rows) 
	{
        if(err)
        {
          	console.log('[SELECT ERROR] - ',err.message);
          	connection.end();
			var result = { selectSuccess: false}
		   	onGet(result)	
		   	return   
        }        
        console.log('[SELECT success] - ',rows);  

		connection.end();
		var result = { selectSuccess: true, body: rows[0]}
		onGet(result)	   
	});

}


exports.getInterfaceInfoByLibCampaign = function getInterfaceInfoByLibCampaign(campaign_id, onGet)  
{
	var lib_campaign // 在异步回调里任然可以访问 闭包 闭包延长了变量的生命周期
	var lib_offer
	var env = this
	function onGetLibOfferDataOfSql(result)
	{
		if(result.selectSuccess && result.body) 
		{
			lib_offer = result.body
			var interfaceInfo 				   = env.getInterfaceInfoDefault()   
			interfaceInfo.pid                  = lib_campaign.aff_id                    
			interfaceInfo.campid               = lib_campaign.campaign_id               
			interfaceInfo.offer_name           = lib_offer.offer_name           
			interfaceInfo.state                = lib_campaign.state    
			interfaceInfo.tips                 = lib_campaign.tips 
			interfaceInfo.platform             = lib_offer.platform               
			interfaceInfo.price_model          = lib_offer.pay_type           
			interfaceInfo.price                = lib_campaign.payout  

			var a = lib_campaign.geo_country
			var b = lib_campaign.geo_country_except
			if (a  == 'AO AF AL DZ AD AI AG AR AM AU AT AZ BS BH BD BB BY BE BZ BJ BO BW BR BN BG BF BM BT MM BI CM CA CF TD CL CN CO CG KH CR CU CY CZ CK DK DJ DO EC EG SV EE ET FJ FI FR GF GA GM GE DE GH GI GR GD GU GT GN GY HT HN HK HU IS IN ID IR IQ IE IL IT KT JM JP JO KZ KE KR KW KG KH LA LV LB LS LR LY LI LT LU MO MG MW MS MY MV ML MT MU MR MX MD MC MN MA MZ MM NA NZ NR NP NL NI NE NG KP NO OM PK PA PG PY PE PH PL PF PT PR QA RO RU LC VC SM ST SA SN SC SL SG SK SI SB SO ZA ES LK LC VC SD SR SZ SE CH SY TW TJ TZ TH TG TO TT TL TN TR TM UG UA AE GB US UY UZ TZ VE VN YE YU ZA ZW ZR ZM')
				a = 'global'
			if (b == '' || b == null)
				b = ''
			else
				b = ' except ' + b
			interfaceInfo.geo                  = a + b

			interfaceInfo.tracking_link        = lib_campaign.tracking_link          
			interfaceInfo.preview_link         = lib_offer.preview_link          
			interfaceInfo.start_date           = lib_campaign.start_date            
			interfaceInfo.end_date             = lib_campaign.end_date              
			interfaceInfo.performance_criteria = lib_offer.kpi  
			interfaceInfo.requirement_traffic  = lib_offer.traffic_reqirement   
			interfaceInfo.daily_cap            = lib_campaign.cap_all_today              
			interfaceInfo.creative_link        = 'http=//game520.com/offer/creatives/' + lib_campaign.campaign_id + '/' + lib_campaign.campaign_id + '.zip'        

			var result_getInterfaceInfoByLibCampaign = {selectSuccess: true, body: interfaceInfo}
		   	onGet(result_getInterfaceInfoByLibCampaign)	
		}
		else
		{
			var result_getInterfaceInfoByLibCampaign = {selectSuccess: false}
		   	onGet(result_getInterfaceInfoByLibCampaign)	
		}
	}


	function onGetLibCampaignDataOfSql(result)
	{
		if(result.selectSuccess && result.body) 
		{
			lib_campaign = result.body
			console.log("lib_campaign ofid:",lib_campaign.offer_id)
			env.getLibOfferDataOfSql(lib_campaign.offer_id,onGetLibOfferDataOfSql) 
		}
		else
		{
			var result_getInterfaceInfoByLibCampaign = {selectSuccess: false}
		   	onGet(result_getInterfaceInfoByLibCampaign)	
		}
	}

	this.getLibCampaignDataOfSql(campaign_id,onGetLibCampaignDataOfSql) 
}

exports.writeInterfaceInfoToSql = function writeInterfaceInfoToSql(data, onWrite)
{	

	//过滤非录入数据
	data.id = null

	//填充data
	var c = 'http://game520.com/offer/creatives/' + data.campid + '/' + data.campid + '.zip' 
	if (!data.creative_link)
		data.creative_link = c
	

	var connection = getSqlConnection()
	connection.connect();  
	//执行SQL语句
	var element =['id','pid','campid','offer_name','status','state','tips','platform','price_model','price','geo','tracking_link','preview_link','start_date','end_date','performance_criteria','requirement_traffic','effective_date','update_date','app_category','exclude_device','exclude_site','carriers','daily_cap','creative_link','icon_link','min_android_version','max_android_version','app_name','app_desc','app_size','app_rate']
	var sql1 = '('
	for (var i=0;i<element.length;i++)
	{
		sql1 = sql1 + element[i] 
		if (i == element.length -1)
			sql1 = sql1 + ')'
		else
			sql1 = sql1 + ','
	}
	console.log('INSERT sql1:',sql1);     
	var  sql = 'INSERT INTO interface_info_v1' + sql1 + 'VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
	var  params = [];
	for (var i=0;i<element.length;i++)
	{	

		params[i] = data[element[i]] 
		console.log('INSERT params:',params[i]);     
	}

	connection.query(sql,params, 
		function(err, rows) 
		{ 
		    if (err) 
		    {
		        console.log('INSERT error:',err.message);
		        connection.end();
		      	var result =  {insertSuccess: false,message:'insert interface_info_v1 fail'}
		      	onWrite(result)
		      	return
		    }
		    console.log('INSERT success:',rows,data.offer_name);    
		    connection.end();

			var fs = require("fs")

			fs.readFile('./public/emailBody.txt', function (err, fileData) { 
				var emailTitle = '<br><br>New offer ['+data.offer_name+'] ad688-'+data.campid+'- AD688.COM<br><br>'
				var emailbody = fileData.toString()

				var reg = new RegExp("\r\n","g"); 
				data.geo= data.geo.replace(reg,"<br>")
				data.requirement_traffic= data.requirement_traffic.replace(reg,"<br>") 
				data.performance_criteria= data.performance_criteria.replace(reg,"<br>")

				emailbody = emailbody.replace('XXXcampidXXX', data.campid)
				emailbody = emailbody.replace('XXXoffer_nameXXX', data.offer_name)
				emailbody = emailbody.replace('XXXgeoXXX', data.geo)
				emailbody = emailbody.replace('XXXcamType1XXX', camType1Table[data.platform])
				emailbody = emailbody.replace('XXXcamType2XXX', 'non incent')	
				emailbody = emailbody.replace('XXXplatform1XXX', data.platform)	
				emailbody = emailbody.replace('XXXplatform2XXX', data.platform)	
				var cap = data.daily_cap
				if (cap >= 1000000)
					cap = "OPEN"
				emailbody = emailbody.replace('XXXdaily_capXXX', cap)
				emailbody = emailbody.replace('XXXpriceXXX', data.price)
				emailbody = emailbody.replace('XXXrequirement_trafficXXX', data.requirement_traffic)
				emailbody = emailbody.replace('XXXperformance_criteriaXXX', data.performance_criteria)
				emailbody = emailbody.replace('XXXtracking_linkXXX', data.tracking_link)
				emailbody = emailbody.replace('XXXpreview_linkXXX', data.preview_link)

			    var result = { insertSuccess: true, message:'insert interface_info_v1 success' + emailTitle+emailbody}
		    	onWrite(result)	
			});

		}); 
}

//////////////allData////////////////////////////
exports.getAllDataDataDefault =  function getAllDataDataDefault()
{
  var allData = {
		offer_name:dataDefaultTable.offer_name,   
		group_id:dataDefaultTable.group_id, 
		advertiser_id:dataDefaultTable.advertiser_id,
		adv_camp_id:dataDefaultTable.adv_camp_id,       
		//first_pay_in:0, 等于paiyin
		pay_type:dataDefaultTable.pay_type,   
		redirect_format:dataDefaultTable.redirect_format, //创建liboffer后 更新（加后缀）
		start_date:dataDefaultTable.start_date,    
		end_date:dataDefaultTable.end_date,   
		geo_country:dataDefaultTable.geo_country,
		geo_city:dataDefaultTable.geo_city,          
		geo_country_except:dataDefaultTable.geo_country_except,
		geo_city_except:dataDefaultTable.geo_city_except, 
		cap_all_today:dataDefaultTable.cap_all_today,
		cap_all:dataDefaultTable.cap_all,  //不填则默认和cap all today 一样
		ret_day_need:dataDefaultTable.ret_day_need,      
		ret_percent_need:dataDefaultTable.ret_percent_need, 
		quality0:dataDefaultTable.quality0,          
		quality1:dataDefaultTable.quality1,          
		quality2:dataDefaultTable.quality2,          
		quality3:dataDefaultTable.quality3,          
		quality4:dataDefaultTable.quality4,          
		quality5:dataDefaultTable.quality5,
		traffic_type:dataDefaultTable.traffic_type,      
		traffic_reqirement:dataDefaultTable.traffic_reqirement,
		kpi:dataDefaultTable.kpi,
		platform:dataDefaultTable.platform,          
		devices:dataDefaultTable.devices, 
		osversion:dataDefaultTable.osversion,  
		appid:dataDefaultTable.appid,
		operator:dataDefaultTable.operator,
		payment_terms:dataDefaultTable.payment_terms,
		preview_link:dataDefaultTable.preview_link,  
		pack_res_link:dataDefaultTable.pack_res_link,     
		slogan_description:dataDefaultTable.slogan_description,
		remark0:dataDefaultTable.remark0,
		remark1:dataDefaultTable.remark1,
	
		//lib_campaign
		//offer_id:null, 创建后生成
		aff_id:dataDefaultTable.aff_id,
		payin:dataDefaultTable.payin, 
		payout:dataDefaultTable.payout,
		state:dataDefaultTable.state,
		cap_exceed_count:dataDefaultTable.cap_exceed_count,  
		cap_exceed_percent:dataDefaultTable.cap_exceed_percent,
		force_stop_level:dataDefaultTable.force_stop_level,  
		force_stop:dataDefaultTable.force_stop,      
	
		//interface_info_v1
		//pid:dataDefaultTable.pid,  等于aff_id
		//campid:null, 创建后生成
		status:dataDefaultTable.status ,     
		tips:dataDefaultTable.tips,
		//price_model:'cpi',	等于pay_type 
		//price:0,    等于payout 
		//geo:'',  等于geo_country
		//tracking_link:'', 创建后生成
		//performance_criteria:'', 等于kpi
		//requirement_traffic:'', 等于traffic_reqirement
		effective_date: dataDefaultTable.effective_date,        
		update_date: dataDefaultTable.update_date ,  
		app_category:dataDefaultTable.app_category,
		exclude_device:dataDefaultTable.exclude_device,     
		exclude_site:dataDefaultTable.exclude_site,  
		carriers:dataDefaultTable.carriers,
		//daily_cap:'这里填真实值*100', 等于cap_all_today
		creative_link:dataDefaultTable.creative_link,
		icon_link:dataDefaultTable.icon_link,           
		min_android_version:dataDefaultTable.min_android_version, 
		max_android_version:dataDefaultTable.max_android_version, 
		app_name:dataDefaultTable.app_name,            
		app_desc:dataDefaultTable.app_desc,            
		app_size:dataDefaultTable.app_size,            
		app_rate:dataDefaultTable.app_rate, 

  }

 return allData
}

exports.getAllDataByLibOffer =  function getAllDataByLibOffer(offer_id, onGet)
{
	var env = this
	function onGetAllDataByLibOffer(result)
	{
		if(result.selectSuccess && result.body) 
		{	
			var lib_offer = result.body
			var allData = env.getAllDataDataDefault()
			allData.offer_name         =lib_offer.offer_name
			allData.advertiser_id      =lib_offer.advertiser_id
			allData.adv_camp_id        =lib_offer.adv_camp_id
			allData.payin              =lib_offer.first_pay_in
			allData.geo_country        =lib_offer.geo_country
			allData.cap_all_today      =lib_offer.cap_all_today
			allData.preview_link       =lib_offer.preview_link
			allData.appid              =lib_offer.appid
			allData.redirect_format    =lib_offer.redirect_format
			allData.traffic_reqirement =lib_offer.traffic_reqirement
			allData.kpi                =lib_offer.kpi
			allData.platform           =lib_offer.platform
			allData.devices            =lib_offer.devices
			allData.pay_type           =lib_offer.pay_type
			allData.start_date         =lib_offer.start_date
			allData.end_date           =lib_offer.end_date
			allData.cap_all            =lib_offer.cap_all
			allData.geo_country_except =lib_offer.geo_country_except
			allData.geo_city_except    =lib_offer.geo_city_except
			allData.ret_day_need       =lib_offer.ret_day_need
			allData.ret_percent_need   =lib_offer.ret_percent_need
			allData.traffic_type       =lib_offer.traffic_type
			allData.osversion          =lib_offer.osversion
			allData.payment_terms      =lib_offer.payment_terms
			allData.pack_res_link      =lib_offer.pack_res_link
			allData.slogan_description =lib_offer.slogan_description
			allData.group_id           =lib_offer.group_id
			allData.quality0           =lib_offer.quality0
			allData.quality1           =lib_offer.quality1
			allData.quality2           =lib_offer.quality2
			allData.quality3           =lib_offer.quality3
			allData.quality4           =lib_offer.quality4
			allData.quality5           =lib_offer.quality5

			var result_getAllDataByLibOffer = {selectSuccess: true, body: allData}
		   	onGet(result_getAllDataByLibOffer)	
		}
		else
		{
			var result_getAllDataByLibOffer = {selectSuccess: false}
		   	onGet(result_getAllDataByLibOffer)	
		}
	}

	this.getLibOfferDataOfSql(offer_id,onGetAllDataByLibOffer) 
}

exports.writeAllDataToSql = function writeAllDataToSql(data, onWrite)
{	
	//填充data
	data.first_pay_in = data.payin  
	if (data.cap_all==='')
		data.cap_all = data.cap_all_today 
	data.pid  =  data.aff_id  
	data.price_model = data.pay_type 
	if (data.payout==='')
		data.payout = data.payin 
	data.price = data.payout


	var a = data.geo_country
	var b = data.geo_country_except

	if (b == '' || b == null)
		b = ''
	else
		b = ' except ' + b
	data.geo                  = a + b
	data.performance_criteria = data.kpi
	data.requirement_traffic = data.traffic_reqirement
	data.daily_cap  =  data.cap_all_today  


	var env = this
	var result_writeAllDataToSql = {
		result_libOffer:'',
		result_libCampaign:'',	
		result_interfaceInfo:'',
	}
	function onWriteLibOfferToSql(result)
	{
		result_writeAllDataToSql.result_libOffer = result
		if(result.insertSuccess)
		{
			data.offer_id = result.outData.offer_id	
			data.redirect_format = data.redirect_format + (redirect_formatTable[data.advertiser_id] || '&aff_sub={click_id}&aff_sub2={aff_pub}')     

			env.writeLibCampaignToSql(data, onWriteLibCampaignToSql)
		}
		else
		{
			result_writeAllDataToSql.result_libCampaign = {message:''}
			result_writeAllDataToSql.result_interfaceInfo = {message:''}
			onWrite(result_writeAllDataToSql)
		}
	}

	function onWriteLibCampaignToSql(result)
	{
		result_writeAllDataToSql.result_libCampaign = result
		if(result.insertSuccess)
		{
			data.campaign_id = result.outData.campaign_id	
			data.tracking_link = result.outData.tracking_link	
			data.postback_format = result.outData.postback_format	
			data.campid = result.outData.campaign_id	

			env.writeInterfaceInfoToSql(data, onWriteInterfaceInfoToSql)
		}
		else
		{
			result_writeAllDataToSql.result_interfaceInfo = {message:''}
			onWrite(result_writeAllDataToSql)
		}
	}

	function onWriteInterfaceInfoToSql(result)
	{
		result_writeAllDataToSql.result_interfaceInfo = result
		onWrite(result_writeAllDataToSql)
	}

	env.writeLibOfferToSql(data, onWriteLibOfferToSql)

}